﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Web;
using System.Data;
using DataServer.DTO;

namespace DataServer.DAO
{
    public class NghiPhepDAO : DataProvider
    {
        public List<string> GetColName()
        {
            List<string> cols = new List<string>();
            string query = "SELECT * FROM NGHIPHEP";
            DataTable dt = new DataTable();
            dt = executeQuery(query);
            for (int i = 0; i < dt.Columns.Count; i++)
            {
                cols.Add(dt.Columns[i].ToString());
            }
            return cols;
        }
        public List<NghiPhepDTO> LayDanhSachNgayNghi()
        {

            string query = "SELECT * FROM NGHIPHEP ORDER BY ID ASC";
            //adapter = new OleDbDataAdapter(query, connection);
            //DataTable dt = new DataTable();
            //adapter.Fill(dt);
            DataTable dt = new DataTable();
            dt = executeQuery(query);
            List<NghiPhepDTO> result = new List<NghiPhepDTO>();

            for (int i = 0; i < dt.Rows.Count; i++)
            {
                NghiPhepDTO nn = (NghiPhepDTO)GetDataFromDataRow(dt, i);
                result.Add(nn);
            }

            return result;
        }

        protected override object GetDataFromDataRow(DataTable dt, int i)
        {
            NghiPhepDTO nn = new NghiPhepDTO();
            nn.ID = (int)dt.Rows[i]["ID"];
            nn.MANV = (int)dt.Rows[i]["maNV"];
            nn.Thang = (int)dt.Rows[i]["Thang"];
            nn.Nam = (int)dt.Rows[i]["Nam"];
            nn.SoNgayNghi = (int)dt.Rows[i]["SoNgayNghi"];

            return nn;
        }

        public NghiPhepDTO LayNgayNghi(int id)
        {

            string query = "SELECT * FROM NGHIPHEP WHERE ID = '" + id.ToString() + "' ORDER BY ID ASC";
            //adapter = new OleDbDataAdapter(query, connection);
            //DataTable dt = new DataTable();
            //adapter.Fill(dt);
            DataTable dt = new DataTable();
            dt = executeQuery(query);
            List<NghiPhepDTO> result = new List<NghiPhepDTO>();

            for (int i = 0; i < dt.Rows.Count; i++)
            {
                NghiPhepDTO nn = (NghiPhepDTO)GetDataFromDataRow(dt, i);
                result.Add(nn);
            }

            return result[0];
        }

        public List<NghiPhepDTO> getDsNgayNghiCuaNhanVien(int maNV)
        {
            string query = "SELECT * FROM NGHIPHEP WHERE MANV = '" + maNV.ToString() + "' ORDER BY ID ASC";
            //adapter = new OleDbDataAdapter(query, connection);
            //DataTable dt = new DataTable();
            //adapter.Fill(dt);
            DataTable dt = new DataTable();
            dt = executeQuery(query);
            List<NghiPhepDTO> result = new List<NghiPhepDTO>();

            for (int i = 0; i < dt.Rows.Count; i++)
            {
                NghiPhepDTO nn = (NghiPhepDTO)GetDataFromDataRow(dt, i);
                result.Add(nn);
            }

            return result;
        }

        public List<NghiPhepDTO> LayDanhSachNgayNghi(int MANV, int Thang, int Nam)
        {

            string query = "SELECT * FROM NGHIPHEP WHERE maNV = '" + MANV.ToString() + "' AND Thang = " + Thang + " AND Nam = " + Nam + " ORDER BY ID ASC";
            //adapter = new OleDbDataAdapter(query, connection);
            //DataTable dt = new DataTable();
            //adapter.Fill(dt);
            DataTable dt = new DataTable();
            dt = executeQuery(query);
            List<NghiPhepDTO> result = new List<NghiPhepDTO>();

            for (int i = 0; i < dt.Rows.Count; i++)
            {
                NghiPhepDTO nn = (NghiPhepDTO)GetDataFromDataRow(dt, i);
                result.Add(nn);
            }

            return result;
        }

        public bool ThemNgayNghi(NghiPhepDTO nn)
        {

            string query = "INSERT INTO NGHIPHEP(maNV, Thang, Nam, SoNgayNghi) VALUES('"
                //+ nn.ID + "','"
                + nn.MANV + "','"
                + nn.Thang + "','"
                + nn.Nam + "','"
                + nn.SoNgayNghi + "')";
            try
            {
                executeNonQuery(query);
                return true;
            }
            catch (System.Exception e)
            {
                return false;
            }
        }

        public bool XoaNgayNghi(int ID)
        {
            string query = "DELETE FROM NGHIPHEP WHERE ID = '" + ID.ToString() + "'";
            try
            {
                executeNonQuery(query);
                return true;
            }
            catch (System.Exception e)
            {
                return false;
            }
        }

        public bool SuaNgayNghi(int id, NghiPhepDTO nn)
        {
            string query = "UPDATE NGHIPHEP SET maNV = '" + nn.MANV
                + "', Thang = '" + nn.Thang
                + "', Nam = '" + nn.Nam
                + "', SoNgayNghi = '" + nn.SoNgayNghi
                + "' WHERE ID = '" + id + "'";

            try
            {
                executeNonQuery(query);
                return true;
            }
            catch (System.Exception e)
            {
                return false;
            }
        }
    }
}